import scrapy
import re
import pymysql


class Book():
    title = ''
    author = ''
    clickTotal = 0
    clickMonth = 0
    clickWeek = 0
    recommendTotal = 0
    recommendMonth = 0
    recommendWeek = 0
    monthTicket = 0
    monthTicketTotal = 0
    blade = 0
    bladeTotal = 0
    wordTotal = 0


def stringToInt(string):
    patten = "[-+]?[0-9]*\.?[0-9]+"
    a = float(re.search(patten, string).group())
    if '万' in string:
        a = a * 10000
    return int(a)


class BookSpider(scrapy.Spider):
    name = 'books'
    start_urls = ['https://www.ciweimao.com/book/100100123']

    def parse(self, response):
        previous = ''
        book = Book()

        book_title = '.book-title'
        for book_title in response.css(book_title):
            titlecss = 'h1 ::text'
            book.title = book_title.css(titlecss).extract_first()
            authorcss = 'p a ::text'
            book.author = book_title.css(authorcss).extract_first()

        book_property = '.book-property'
        for book in response.css(book_property):
            click_total_selector = 'span ::text'
            for text in book.css(click_total_selector):
                yield {}
                string = text.extract()
                if string != '◆':
                    if '总推荐' in previous:
                        book.recommendTotal = stringToInt(string)
                    elif '月推荐' in previous:
                        book.recommendMonth = stringToInt(string)
                    elif '周推荐' in previous:
                        book.recommendWeek = stringToInt(string)

                    if '总点击' in string:
                        book.clickTotal = stringToInt(string)
                    elif '月点击' in string:
                        book.clickMonth = stringToInt(string)
                    elif '周点击' in string:
                        book.clickWeek = stringToInt(string)
                    elif '总月票' in string:
                        book.monthTicketTotal = stringToInt(string)
                    elif '当月月票' in string:
                        book.monthTicket = stringToInt(string)
                    elif '总刀片' in string:
                        book.bladeTotal = stringToInt(string)
                    elif '月刀片' in string:
                        book.blade = stringToInt(string)
                    elif '完成字数' in string:
                        book.wordTotal = stringToInt(string)

                    previous = string

        yield {

            insertBook(book.title, book.author, book.clickTotal, book.clickMonth, book.clickWeek, book.recommendTotal,
                       book.recommendMonth, book.recommendWeek,
                       book.monthTicket,
                       book.monthTicketTotal,
                       book.blade, book.bladeTotal, book.wordTotal)
        }


def insertBook(title, author, clickTotal, clickMonth, clickWeek, recommendTotal, recommendMonth, recommendWeek,
               monthTicket,
               monthTicketTotal,
               blade, bladeTotal, wordTotal):
    db = pymysql.connect(host='localhost', user='root', passwd='A1UH6kDp0Fxc', db='hbooker', charset='utf8')

    cursor = db.cursor()

    sql = "INSERT INTO hbooker.book(Title,Author,ClickTotal,ClickMonth,ClickWeek,RecommendTotal,RecommendMonth,RecommendWeek,MonthTicket,MonthTicketTotal, \
    Blade, BladeTotal, WordTotal) values('%s', '%s', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i')\
    " % \
          (title, author, clickTotal, clickMonth, clickWeek, recommendTotal, recommendMonth, recommendWeek,
           monthTicket,
           monthTicketTotal,
           blade, bladeTotal, wordTotal)

    try:
        # 执行sql语句
        cursor.execute(sql)
        db.commit()
    except:
        print('hello')

    db.close()
